%macro sample_select ; 

/* this macro creates CTE flag  */
%macro cte_flag ; 

degcip_2dig in ('10','11','12','13','15','19',
                '43','44','46','47','48','49','51','52') 


%mend ;


data graduates (keep= pik degcip_2dig opeid year_grad quarter_grad qtime_grad
					deglevl_code state flag_cte) /view=graduates;

    merge INPUTS.pseo_ny_suny_2002_2019_deg_rsch (in=a drop=year_grad) 
          INPUTS.pseo_ny_suny_2002_2019_deg_pik (in=b) ;
    by dqb_source_id ; 
    
    state = '36' ;
    
    
    
    date_grad_num = input(date_grad,yymmdd10.) ;
    
    quarter_grad = qtr(date_grad_num) ;
    year_grad = year(date_grad_num) ;
    qtime_grad = %qtime(year_grad,quarter_grad,location=datastep) ;
    
    degcip_2dig = substr(degcip,1,2) ;
    flag_cte = %cte_flag;
    if deglevl_code in ('01','02','03','04') ;
    
    if year_grad>=2001 ; 
run;

proc freq data=INPUTS.pseo_ny_suny_2002_2020_enr_rsch ; 
	tables Term ; 
	title 'Terms' ;
run;

data nongraduates (keep = pik degcip_2dig opeid  credithours year 
						quarter state cte_flag deglevl_code )
    /view=nongraduates;
    
    merge INPUTS.pseo_ny_suny_2002_2020_enr_rsch (in=a 
								rename=(deglevl_code = deglevl year=year_char)) 
          INPUTS.pseo_ny_suny_2002_2020_enr_qpik (in=b rename=(qpik_ssn = pik)) ;
    by dqb_source_id ; 
    
    state = '36' ; 
    deglevl_code=put(deglevl,2.);
	year=input(year_char,4.) ;
	if length(deglevl_code) = 1 then deglevl_code = '0'||deglevl_code ; 
        /* need to add qtimes */
		
	if term = "Fall" then quarter = 4 ;
	if term = "Winter" then quarter = 1 ; 
	if term = "Spring" then quarter = 2 ; 
	if term = "Summer" then quarter = 3 ; 
	degcip_2dig = substr(degcip,1,2) ;
    cte_flag=%cte_flag;
    
    if year>=2001 & deglevl_code in ('01','02','03','04') ;
    credithours = input(credit_hours,best4.) ;
run;


     /* need to get max credit hours */ 
proc sort data=nongraduates out=nongrads; 
	by pik opeid deglevl_code year quarter ; 
run;
            
data OUTPUTS.nongrads_qa_suny ; 
            set nongrads; 
            run;

proc print data=nongrads (obs=10) ;
	var pik opeid deglevl_code year quarter credithours ; 
run;
    
proc summary data=nongrads nway ;
    class pik opeid deglevl_code ;
    var credithours cte_flag;
    output out=maxcrd (drop = _TYPE_ _FREQ_) 
            max(cte_flag)= cte_flag
            sum(credithours)=maxcrd ;
run;

proc freq data=nongrads ; 
	tables cte_flag ;
	title 'cte flag in nongrads' ;
run;
    
proc sort data=nongrads ;
    by pik opeid deglevl_code ; 
run;
    
proc print data=maxcrd (obs=50) ; 
	title 'maxcrd observations' ;
run;
            
proc freq data=maxcrd ; 
            tables cte_flag; 
run;

data nongraduates_finalenroll (drop=credithours rename=(cte_flag = flag_cte)) 
            OUTPUTS.nongrads_suny; 
    merge nongrads (in=a drop=cte_flag) maxcrd (in=b) ;
    by pik opeid deglevl_code;
    
    if maxcrd >=10 ;
            
    *if first.deglevl_code then flag_cte = 0 ;
    *if flag_cte = 0  and cte_flag = 1 then flag_cte = 1 ;
            
    if last.deglevl_code and cte_flag = 1 then output; 
run;
            
proc sort data=graduates out=grads nodupkey ;
     by pik opeid deglevl_code ; 
run;
            
proc freq data=grads ;
      title 'CTE FLAG GRADS ' ;
      tables flag_cte ; 
run;

data OUTPUTS.all_students_suny (drop=maxcrd cte_flag); 
       merge grads (in=a) 
             nongraduates_finalenroll ; 
       by pik opeid deglevl_code ; 
            
       graduate = a ; 
       credithours = maxcrd;
            
       if (flag_cte and graduate) or not graduate ;
run;
            
proc freq data=OUTPUTS.all_students_suny ;
            tables graduate ; 
run;            

%mend sample_select;  
%sample_select;
